package com.anolesoft.twelvePlan.mapper;

import cn.hutool.core.util.StrUtil;
import com.anolesoft.twelvePlan.dto.MesGyInfoToPlanDTO;
import com.anolesoft.twelvePlan.dto.MesPlanXiadaJixingDTO;
import com.anolesoft.twelvePlan.dto.PlanFenXiJxDTO;
import com.anolesoft.twelvePlan.entity.MesGyInfoDO;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;

/**
 * @author zyq
 * @date 2022/3/15
 */
@Mapper
public interface MesGyInfoMapper extends BaseMapper<MesGyInfoDO> {
    @SelectProvider(type = createSQL.class, method = "queryGyInfoSQL")
    List<MesGyInfoDO> queryGyInfo(@Param("mesGyInfoDO") MesGyInfoDO mesGyInfoDO, Page<MesGyInfoDO> page);
    public static class createSQL {
        public static String queryGyInfoSQL(@Param("mesGyInfoDO") MesGyInfoDO mesGyInfoDO, Page<MesGyInfoDO> page){
            return new SQL(){
                {
                    StringBuffer sql = new StringBuffer();
                    //sql.append(" a.objuid, a.deliverytype, a.jx, a.parentpartid, a.partid, a.djnum, a.innerwork, a.processroute, ");
                    //sql.append(" a.parttype, a.jsworkshop, a.deliveryorder, a.llworkshop, a.lldeliveryorder, a.partname, a.danzhuangnum, ");
                    //sql.append(" a.longperiodpart, a.jmjcategory, a.productfamily, a.processorsnew, a.junjian, a.materialbrand, ");
                    //sql.append(" a.materialbrandstate, a.materialspecification, a.length, a.width, a.height, a.forgedcastingscode, ");
                    //sql.append(" a.partproperty, a.heattreattype, a.bfcl, a.painting, a.wash, a.specialinspect, a.sandblasting,");
                    //sql.append(" a.relievestress, a.electropolishing, a.others, a.weight, a.remarks, a.createuid, ");
                    //sql.append(" a.createtime, a.modifyuid, a.modifytime, a.deleteuid, a.deletetime, a.factory_id ");
                    sql.append(" a.objuid, a.deliverytype, a.jx, a.parentpartid, a.partid, a.djnum, a.innerwork, a.processroute,");
                    sql.append(" a.parttype, a.jsworkshop, a.deliveryorder, a.llworkshop, a.lldeliveryorder, a.partname, a.danzhuangnum,");
                    sql.append(" a.longperiodpart, a.jmjcategory, a.productfamily, a.processorsnew, a.junjian, a.materialbrand,");
                    sql.append(" a.materialbrandstate, a.materialspecification, a.length, a.width, a.height, a.forgedcastingscode,");
                    sql.append(" a.partproperty, a.heattreattype, a.bfcl, a.painting, a.wash, a.specialinspect, a.sandblasting,");
                    sql.append(" a.relievestress, a.electropolishing, a.others, a.weight, a.remarks, a.factory_id, a.lltotal,");
                    sql.append(" b.serialnumber from mes_gy_info a left join");
                    sql.append(" (select b.mes_gy_info_obj, b.serialnumber from mes_gy_info_more b where b.nianyue = to_char(sysdate,'yyyy-mm')) b");
                    sql.append(" on a.objuid = b.mes_gy_info_obj ");
                    sql.append(" where a.deleteflag = 0 ");
                    sql.append(" and a.factory_id = #{mesGyInfoDO.factoryId, jdbcType=VARCHAR} ");
                    if (!StrUtil.isEmptyOrUndefined(mesGyInfoDO.getJx())){
                        sql.append(" and a.jx = trim(upper(#{mesGyInfoDO.jx, jdbcType=VARCHAR})) ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(mesGyInfoDO.getPartid())){
                        sql.append(" and a.partid = trim(upper(#{mesGyInfoDO.partid, jdbcType=VARCHAR})) ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(mesGyInfoDO.getPartname())){
                        sql.append(" and a.partname = trim(#{mesGyInfoDO.partname, jdbcType=VARCHAR}) ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(mesGyInfoDO.getJmjcategory())){
                        sql.append(" and a.jmjcategory = trim(#{mesGyInfoDO.jmjcategory, jdbcType=VARCHAR}) ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(mesGyInfoDO.getLongperiodpart())){
                        sql.append(" and a.longperiodpart = trim(#{mesGyInfoDO.longperiodpart, jdbcType=VARCHAR}) ");
                    }
                    sql.append(" order by a.jx, b.serialnumber");
                    SELECT(sql.toString());
                    System.out.println("queryGyInfoSQL:" + sql.toString());
                }
            }.toString();
        }
    }

    @SelectProvider(type = createSQL2.class, method = "repeatGyInfoSQL")
    int repeatGyInfo(@Param("mesGyInfoDO") MesGyInfoDO mesGyInfoDO);
    public static class createSQL2 {
        public static String repeatGyInfoSQL(@Param("mesGyInfoDO") MesGyInfoDO mesGyInfoDO){
            return new SQL(){
                {
                    StringBuffer sql = new StringBuffer();
                    sql.append(" count(*) from mes_gy_info a where a.jx = trim(upper(#{mesGyInfoDO.jx, jdbcType=VARCHAR})) ");
                    sql.append(" and a.partid = trim(upper(#{mesGyInfoDO.partid, jdbcType=VARCHAR})) ");
                    sql.append(" and a.factory_id = #{mesGyInfoDO.factoryId, jdbcType=VARCHAR} ");
                    sql.append(" and a.jsworkshop = #{mesGyInfoDO.jsworkshop, jdbcType=VARCHAR}");
                    sql.append(" and a.deliveryorder = #{mesGyInfoDO.deliveryorder, jdbcType=VARCHAR}");
                    sql.append(" and a.deleteflag = 0");
                    if (StrUtil.isEmptyOrUndefined(mesGyInfoDO.getParentpartid())){
                        sql.append(" and a.parentpartid is null ");
                    } else {
                        sql.append(" and a.parentpartid = trim(upper(#{mesGyInfoDO.parentpartid, jdbcType=VARCHAR})) ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(mesGyInfoDO.getObjuid())){
                        sql.append(" and a.objuid != #{mesGyInfoDO.objuid, jdbcType=VARCHAR}");
                    }
                    SELECT(sql.toString());
                    System.out.println(">>>>repeatGyInfoSQL:" + sql.toString());
                }
            }.toString();
        }
    }

    @Insert("insert into mes_gy_info(deliverytype, jx, parentpartid, partid, djnum, innerwork, processroute," +
            " parttype, jsworkshop, deliveryorder, llworkshop, lldeliveryorder, partname, danzhuangnum," +
            " longperiodpart, jmjcategory, productfamily, processorsnew, junjian, materialbrand," +
            " materialbrandstate, materialspecification, length, width, height, forgedcastingscode," +
            " partproperty, heattreattype, bfcl, painting, wash, specialinspect, sandblasting," +
            " relievestress, electropolishing, others, weight, remarks, createuid," +
            " createtime, factory_id) values (" +
            " #{mesGyInfoDO.deliverytype, jdbcType=VARCHAR}, trim(upper(#{mesGyInfoDO.jx, jdbcType=VARCHAR})), trim(upper(#{mesGyInfoDO.parentpartid, jdbcType=VARCHAR}))," +
            " trim(upper(#{mesGyInfoDO.partid, jdbcType=VARCHAR})), #{mesGyInfoDO.djnum, jdbcType=INTEGER}, #{mesGyInfoDO.innerwork, jdbcType=VARCHAR}," +
            " #{mesGyInfoDO.processroute, jdbcType=VARCHAR}, #{mesGyInfoDO.parttype, jdbcType=VARCHAR}, #{mesGyInfoDO.jsworkshop, jdbcType=VARCHAR}," +
            " #{mesGyInfoDO.deliveryorder, jdbcType=VARCHAR}, #{mesGyInfoDO.llworkshop, jdbcType=VARCHAR}, #{mesGyInfoDO.lldeliveryorder, jdbcType=VARCHAR}," +
            " #{mesGyInfoDO.partname, jdbcType=VARCHAR}, #{mesGyInfoDO.danzhuangnum, jdbcType=INTEGER}, trim(#{mesGyInfoDO.longperiodpart, jdbcType=VARCHAR})," +
            " trim(#{mesGyInfoDO.jmjcategory, jdbcType=VARCHAR}), #{mesGyInfoDO.productfamily, jdbcType=VARCHAR}, #{mesGyInfoDO.processorsnew, jdbcType=VARCHAR}," +
            " #{mesGyInfoDO.junjian, jdbcType=VARCHAR}, #{mesGyInfoDO.materialbrand, jdbcType=VARCHAR}, #{mesGyInfoDO.materialbrandstate, jdbcType=VARCHAR}," +
            " #{mesGyInfoDO.materialspecification, jdbcType=VARCHAR}, #{mesGyInfoDO.length, jdbcType=VARCHAR}, #{mesGyInfoDO.width, jdbcType=VARCHAR}," +
            " #{mesGyInfoDO.height, jdbcType=VARCHAR}, #{mesGyInfoDO.forgedcastingscode, jdbcType=VARCHAR}, #{mesGyInfoDO.partproperty, jdbcType=VARCHAR}," +
            " #{mesGyInfoDO.heattreattype, jdbcType=VARCHAR}, #{mesGyInfoDO.bfcl, jdbcType=VARCHAR}, #{mesGyInfoDO.painting, jdbcType=VARCHAR}," +
            " #{mesGyInfoDO.wash, jdbcType=VARCHAR}, #{mesGyInfoDO.specialinspect, jdbcType=VARCHAR}, #{mesGyInfoDO.sandblasting, jdbcType=VARCHAR}," +
            " #{mesGyInfoDO.relievestress, jdbcType=VARCHAR}, #{mesGyInfoDO.electropolishing, jdbcType=VARCHAR}, #{mesGyInfoDO.others, jdbcType=VARCHAR}," +
            " #{mesGyInfoDO.weight, jdbcType=DOUBLE}, #{mesGyInfoDO.remarks, jdbcType=VARCHAR}, #{mesGyInfoDO.createuid, jdbcType=VARCHAR}," +
            " sysdate, #{mesGyInfoDO.factoryId, jdbcType=VARCHAR})")
    int insertGyInfo(@Param("mesGyInfoDO") MesGyInfoDO mesGyInfoDO);

    @Update("update mes_gy_info a set" +
            " a.deliverytype = #{mesGyInfoDO.deliverytype, jdbcType=VARCHAR}, a.jx = trim(upper(#{mesGyInfoDO.jx, jdbcType=VARCHAR}))," +
            " a.parentpartid = trim(upper(#{mesGyInfoDO.parentpartid, jdbcType=VARCHAR})), a.partid = trim(upper(#{mesGyInfoDO.partid, jdbcType=VARCHAR}))," +
            " a.djnum = #{mesGyInfoDO.djnum, jdbcType=INTEGER}, a.innerwork = #{mesGyInfoDO.innerwork, jdbcType=VARCHAR}," +
            " a.processroute = #{mesGyInfoDO.processroute, jdbcType=VARCHAR}, a.parttype = #{mesGyInfoDO.parttype, jdbcType=VARCHAR}," +
            " a.jsworkshop = #{mesGyInfoDO.jsworkshop, jdbcType=VARCHAR}, a.deliveryorder = #{mesGyInfoDO.deliveryorder, jdbcType=VARCHAR}," +
            " a.llworkshop = #{mesGyInfoDO.llworkshop, jdbcType=VARCHAR}, a.lldeliveryorder = #{mesGyInfoDO.lldeliveryorder, jdbcType=VARCHAR}," +
            " a.partname = #{mesGyInfoDO.partname, jdbcType=VARCHAR}, a.danzhuangnum = #{mesGyInfoDO.danzhuangnum, jdbcType=INTEGER}," +
            " a.longperiodpart = trim(#{mesGyInfoDO.longperiodpart, jdbcType=VARCHAR}), a.jmjcategory = trim(#{mesGyInfoDO.jmjcategory, jdbcType=VARCHAR})," +
            " a.productfamily = #{mesGyInfoDO.productfamily, jdbcType=VARCHAR}, a.processorsnew = #{mesGyInfoDO.processorsnew, jdbcType=VARCHAR}," +
            " a.junjian = #{mesGyInfoDO.junjian, jdbcType=VARCHAR}, a.materialbrand = #{mesGyInfoDO.materialbrand, jdbcType=VARCHAR}," +
            " a.materialbrandstate = #{mesGyInfoDO.materialbrandstate, jdbcType=VARCHAR}, a.materialspecification = #{mesGyInfoDO.materialspecification, jdbcType=VARCHAR}," +
            " a.length = #{mesGyInfoDO.length, jdbcType=VARCHAR}, a.width = #{mesGyInfoDO.width, jdbcType=VARCHAR}," +
            " a.height = #{mesGyInfoDO.height, jdbcType=VARCHAR}, a.forgedcastingscode = #{mesGyInfoDO.forgedcastingscode, jdbcType=VARCHAR}," +
            " a.partproperty = #{mesGyInfoDO.partproperty, jdbcType=VARCHAR}, a.heattreattype = #{mesGyInfoDO.heattreattype, jdbcType=VARCHAR}," +
            " a.bfcl = #{mesGyInfoDO.bfcl, jdbcType=VARCHAR}, a.painting = #{mesGyInfoDO.painting, jdbcType=VARCHAR}," +
            " a.wash = #{mesGyInfoDO.wash, jdbcType=VARCHAR}, a.specialinspect = #{mesGyInfoDO.specialinspect, jdbcType=VARCHAR}," +
            " a.sandblasting = #{mesGyInfoDO.sandblasting, jdbcType=VARCHAR}, a.relievestress = #{mesGyInfoDO.relievestress, jdbcType=VARCHAR}," +
            " a.electropolishing = #{mesGyInfoDO.electropolishing, jdbcType=VARCHAR}, a.others = #{mesGyInfoDO.others, jdbcType=VARCHAR}," +
            " a.weight = #{mesGyInfoDO.weight, jdbcType=DOUBLE}, a.remarks = #{mesGyInfoDO.remarks, jdbcType=VARCHAR}," +
            " a.modifyuid = #{mesGyInfoDO.modifyuid, jdbcType=VARCHAR}, a.modifytime = sysdate" +
            " where a.objuid = #{mesGyInfoDO.objuid, jdbcType=VARCHAR}")
    int updateGyInfo(@Param("mesGyInfoDO") MesGyInfoDO mesGyInfoDO);

    @Update("update mes_gy_info a set" +
            " a.deleteflag = 1," +
            " a.deleteuid = #{mesGyInfoDO.deleteuid, jdbcType=VARCHAR}," +
            " a.deletetime = sysdate" +
            " where a.objuid = #{mesGyInfoDO.objuid, jdbcType=VARCHAR}")
    int deleteGyInfo(@Param("mesGyInfoDO") MesGyInfoDO mesGyInfoDO);

    // 导出总条数
    @SelectProvider(type = createSQL3.class, method = "exportGyInfoSQL")
    int exportGyInfo(@Param("mesGyInfoDO") MesGyInfoDO mesGyInfoDO);
    public static class createSQL3 {
        public static String exportGyInfoSQL(@Param("mesGyInfoDO") MesGyInfoDO mesGyInfoDO){
            return new SQL(){
                {
                    StringBuffer sql = new StringBuffer();
                    sql.append(" count(*) from mes_gy_info a left join ");
                    sql.append(" (select b.mes_gy_info_obj, b.serialnumber from mes_gy_info_more b where b.nianyue = to_char(sysdate,'yyyy-mm')) b");
                    sql.append(" on a.objuid = b.mes_gy_info_obj ");
                    sql.append(" where a.deleteflag = 0 ");
                    sql.append(" and a.factory_id = #{mesGyInfoDO.factoryId, jdbcType=VARCHAR} ");
                    if (!StrUtil.isEmptyOrUndefined(mesGyInfoDO.getJx())){
                        sql.append(" and a.jx = trim(upper(#{mesGyInfoDO.jx, jdbcType=VARCHAR})) ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(mesGyInfoDO.getPartid())){
                        sql.append(" and a.partid = trim(upper(#{mesGyInfoDO.partid, jdbcType=VARCHAR})) ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(mesGyInfoDO.getPartname())){
                        sql.append(" and a.partname = trim(#{mesGyInfoDO.partname, jdbcType=VARCHAR}) ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(mesGyInfoDO.getJmjcategory())){
                        sql.append(" and a.jmjcategory = trim(#{mesGyInfoDO.jmjcategory, jdbcType=VARCHAR}) ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(mesGyInfoDO.getLongperiodpart())){
                        sql.append(" and a.longperiodpart = trim(#{mesGyInfoDO.longperiodpart, jdbcType=VARCHAR}) ");
                    }
                    SELECT(sql.toString());
                    System.out.println("exportGyInfoSQL:" + sql.toString());
                }
            }.toString();
        }
    }

    /**
     * 删除所有查询结果
     * @param mesGyInfoDO
     * @return
     */
    @UpdateProvider(type = createSQL6.class, method = "deleteSelectSQL")
    int deleteSelect(@Param("mesGyInfoDO") MesGyInfoDO mesGyInfoDO);
    public static class createSQL6 {
        public static String deleteSelectSQL(@Param("mesGyInfoDO") MesGyInfoDO mesGyInfoDO){
            return new SQL(){
                {
                    StringBuffer sql = new StringBuffer();
                    sql.append(" mes_gy_info a set a.deleteflag = 1,");
                    sql.append(" a.deleteuid = #{mesGyInfoDO.deleteuid, jdbcType=VARCHAR}, a.deletetime = sysdate");
                    sql.append(" where a.deleteflag = 0 ");
                    sql.append(" and a.factory_id = #{mesGyInfoDO.factoryId} ");
                    if (!StrUtil.isEmptyOrUndefined(mesGyInfoDO.getJx())){
                        sql.append(" and a.jx = trim(upper(#{mesGyInfoDO.jx})) ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(mesGyInfoDO.getPartid())){
                        sql.append(" and a.partid = trim(upper(#{mesGyInfoDO.partid})) ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(mesGyInfoDO.getPartname())){
                        sql.append(" and a.partname = trim(#{mesGyInfoDO.partname}) ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(mesGyInfoDO.getJmjcategory())){
                        sql.append(" and a.jmjcategory = trim(#{mesGyInfoDO.jmjcategory}) ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(mesGyInfoDO.getLongperiodpart())){
                        sql.append(" and a.longperiodpart = trim(#{mesGyInfoDO.longperiodpart}) ");
                    }
                    UPDATE(sql.toString());
                    System.out.println("deleteSelectSQL:" + sql.toString());
                }
            }.toString();
        }
    }

    // 计划下达页面查询
    @SelectProvider(type = createSQLs.class, method = "queryPlanSQL")
    List<MesGyInfoToPlanDTO> queryPlan(@Param("mesGyInfoToPlanDTO") MesGyInfoToPlanDTO mesGyInfoToPlanDTO, Page<MesGyInfoToPlanDTO> page);
    public static class createSQLs {
        public static String queryPlanSQL(@Param("mesGyInfoToPlanDTO") MesGyInfoToPlanDTO mesGyInfoToPlanDTO, Page<MesGyInfoToPlanDTO> page){
            return new SQL(){
                {
                    boolean date = false;
                    if (mesGyInfoToPlanDTO.getCheckTime2Date() != null) {
                        date = true;
                    }
                    StringBuffer sql = new StringBuffer();
                    //sql.append(" a.objuid, a.jx, a.partid, a.djnum, a.innerwork, a.processroute, a.longperiodpart, a.deliverytype,");
                    //sql.append(" a.parttype, a.jsworkshop, a.deliveryorder, a.matching, a.grouping_a, a.checktime, a.isexecutable,");
                    //sql.append(" a.delivertotal, a.deliverstate, a.jxmlstock, a.mlstocktotal, a.bcpstocknum, a.bcpstocktotal,");
                    //sql.append(" a.mesprogress, a.outprocess, a.equal, a.waixie, a.waixiefactory, a.tempplannum,");
                    //sql.append(" a.remarks1, a.moreinfo, a.chargeperson, a.endproduct, a.tools, a.equipment, a.technicalquality,");
                    //sql.append(" a.blanking, a.outsourcing, a.outcooperation, a.plantime, a.actualtime, a.ladong, a.parentpartid,");
                    //sql.append(" replace(c.serialnumber, '-', '') as serialnumber, b.name");
                    //sql.append(" from mes_gy_info a left join stf_employee b on b.objuid = a.chargeperson");
                    //sql.append(" inner join mv_mes_gy_info_xuhao c on a.objuid = c.mes_gy_info_obj");
                    //sql.append(" where a.deleteflag = 0 and a.factory_id = #{mesGyInfoToPlanDTO.factoryId, jdbcType=VARCHAR}");
                    sql.append(" replace(b.serialnumber, '-', '') as serialnumber, a.objuid, a.jx, a.parentpartid,");
                    sql.append(" a.partid, a.djnum, a.innerwork, a.processroute, a.longperiodpart, a.deliverytype, a.parttype,");
                    sql.append(" a.factory_id, a.jsworkshop, a.deliveryorder, ");
                    sql.append(" a.delivertotal, a.deliverstate, a.jxmlstock, a.mlstocktotal, a.bcpstocknum, a.bcpstocktotal,");
                    sql.append(" a.mesprogress, a.outprocess, a.equal, a.waixie, a.waixiefactory, a.tempplannum, b.remarks1,");
                    sql.append(" b.moreinfo, a.chargeperson, b.endproduct, b.tools, b.equipment, b.technicalquality, a.blanking,");
                    sql.append(" a.outsourcing, a.outcooperation, a.plantime, a.actualtime, a.ladong, a.state, a.lltotal,");
                    sql.append(" b.parentnum, b.objuid as rowkey, b.xiada,");
                    if (date) {
                        sql.append(" b.matching, b.grouping_a, b.checktime, b.isexecutable, b.jzjh,");
                    }
                    sql.append(" wm_concat(d.name) as name from mes_gy_info a left join ");
                    if (date) {
                        sql.append(" (select b.objuid, b.mes_gy_info_obj, b.serialnumber, b.grouping_a, b.checktime, b.isexecutable, b.jzjh, b.matching, b.lengthnum, b.parentnum, b.nianyue, b.xiada, ");
                        sql.append(" b.remarks1, b.moreinfo, b.endproduct, b.tools, b.equipment, b.technicalquality, b.mes_plan_xiada_id from mes_gy_info_more b");
                        sql.append(" where b.nianyue = to_char(#{mesGyInfoToPlanDTO.checkTime2Date, jdbcType=DATE},'yyyy-mm')) b");
                    } else {
                        sql.append(" (select b.objuid, b.mes_gy_info_obj, b.serialnumber, b.lengthnum, b.parentnum, b.xiada,");
                        sql.append(" b.remarks1, b.moreinfo, b.endproduct, b.tools, b.equipment, b.technicalquality, b.mes_plan_xiada_id from mes_gy_info_more b");
                        sql.append(" where b.nianyue = to_char(sysdate,'yyyy-mm')) b");
                    }
                    sql.append(" on a.objuid = b.mes_gy_info_obj ");

                    //sql.append(" left join stf_employee c on a.chargeperson = c.objuid");
                    sql.append(" left join mes_plan_xiada_fuzeren c on b.mes_plan_xiada_id = c.mes_plan_xiada_uid");
                    sql.append(" left join stf_employee d on c.stf_employee_uid = d.objuid");
                    sql.append(" where a.deleteflag = 0 and a.factory_id = #{mesGyInfoToPlanDTO.factoryId, jdbcType=VARCHAR}");
                    if (!StrUtil.isEmptyOrUndefined(mesGyInfoToPlanDTO.getJx())){
                        sql.append(" and a.jx = trim(upper(#{mesGyInfoToPlanDTO.jx, jdbcType=VARCHAR})) ");
                    }
                    if (!StrUtil.isEmptyOrUndefined(mesGyInfoToPlanDTO.getPartid())){
                        sql.append(" and a.partid = trim(upper(#{mesGyInfoToPlanDTO.partid, jdbcType=VARCHAR})) ");
                    }
                    // 16厂树查询根节点查询
                    if (mesGyInfoToPlanDTO.getFactoryId().equals("16") && StrUtil.isEmptyOrUndefined(mesGyInfoToPlanDTO.getPartid())) {
                        sql.append(" and (b.lengthnum = 9 or b.lengthnum is null)");
                    }
                    sql.append(" group by");
                    sql.append(" b.serialnumber, a.objuid, a.jx, a.parentpartid,");
                    sql.append(" a.partid, a.djnum, a.innerwork, a.processroute, a.longperiodpart, a.deliverytype, a.parttype,");
                    sql.append(" a.factory_id, a.jsworkshop, a.deliveryorder, ");
                    sql.append(" a.delivertotal, a.deliverstate, a.jxmlstock, a.mlstocktotal, a.bcpstocknum, a.bcpstocktotal,");
                    sql.append(" a.mesprogress, a.outprocess, a.equal, a.waixie, a.waixiefactory, a.tempplannum, b.remarks1,");
                    sql.append(" b.moreinfo, a.chargeperson, b.endproduct, b.tools, b.equipment, b.technicalquality, a.blanking,");
                    sql.append(" a.outsourcing, a.outcooperation, a.plantime, a.actualtime, a.ladong, a.state, a.lltotal,");
                    sql.append(" b.parentnum, b.objuid, b.xiada");
                    if (date) {
                        sql.append(" ,b.matching, b.grouping_a, b.checktime, b.isexecutable, b.jzjh");
                    }
                    //sql.append(" left join mes_plan_xiada_fuzeren d on b.mes_plan_xiada_id = ");
                    sql.append(" order by b.serialnumber");
                    //sql.append(" order by c.serialnumber");
                    SELECT(sql.toString());
                    System.out.println("queryPlanSQL:" + sql.toString());
                }
            }.toString();
        }
    }

    // 计划下达树状查询(层数)
    @Select("select max(a.lengthnum) from mes_gy_info_more a where" +
            " a.factory_id = #{mesGyInfoToPlanDTO.factoryId, jdbcType=VARCHAR}" +
            " and a.jx = trim(upper(#{mesGyInfoToPlanDTO.jx, jdbcType=VARCHAR}))")
    Integer queryPlanTreeCount(@Param("mesGyInfoToPlanDTO") MesGyInfoToPlanDTO mesGyInfoToPlanDTO);

    // 计划下达树状查询(子节点)
    @SelectProvider(type = createSQL5.class, method = "queryPlanTreeSQL")
    List<MesGyInfoToPlanDTO> queryPlanTree(@Param("mesGyInfoToPlanDTO") MesGyInfoToPlanDTO mesGyInfoToPlanDTO);
    public static class createSQL5 {
        public static String queryPlanTreeSQL(@Param("mesGyInfoToPlanDTO") MesGyInfoToPlanDTO mesGyInfoToPlanDTO){
            return new SQL(){
                {
                    boolean date = false;
                    if (mesGyInfoToPlanDTO.getCheckTime2Date() != null) {
                        date = true;
                    }
                    StringBuffer sql = new StringBuffer();
                    sql.append(" replace(b.serialnumber, '-', '') as serialnumber, a.objuid, a.jx, a.parentpartid,");
                    sql.append(" a.partid, a.djnum, a.innerwork, a.processroute, a.longperiodpart, a.deliverytype, a.parttype,");
                    sql.append(" a.factory_id, a.jsworkshop, a.deliveryorder, ");
                    sql.append(" b.delivertotal, a.deliverstate, a.jxmlstock, a.mlstocktotal, a.bcpstocknum, a.bcpstocktotal,");
                    sql.append(" a.mesprogress, a.outprocess, a.equal, a.waixie, a.waixiefactory, a.tempplannum, b.remarks1,");
                    sql.append(" b.moreinfo, a.chargeperson, b.endproduct, b.tools, b.equipment, b.technicalquality, a.blanking,");
                    sql.append(" a.outsourcing, a.outcooperation, a.plantime, a.actualtime, a.ladong, a.state, a.lltotal,");
                    sql.append(" b.parentnum, b.objuid as rowkey, b.xiada,");
                    if (date) {
                        sql.append(" b.matching, b.grouping_a, b.checktime, b.isexecutable, b.jzjh,");
                    }
                    sql.append(" wm_concat(d.name) as name from mes_gy_info a left join ");
                    if (date) {
                        sql.append(" (select b.objuid, b.mes_gy_info_obj, b.serialnumber, b.grouping_a, b.checktime, b.isexecutable, b.jzjh, b.matching, b.lengthnum, b.parentnum, b.nianyue, b.xiada, b.delivertotal,");
                        sql.append(" b.remarks1, b.moreinfo, b.endproduct, b.tools, b.equipment, b.technicalquality, b.mes_plan_xiada_id from mes_gy_info_more b");
                        sql.append(" where b.nianyue = to_char(#{mesGyInfoToPlanDTO.checkTime2Date, jdbcType=DATE},'yyyy-mm')) b");
                    } else {
                        sql.append(" (select b.objuid, b.mes_gy_info_obj, b.serialnumber, b.lengthnum, b.parentnum, b.xiada, b.delivertotal,");
                        sql.append(" b.remarks1, b.moreinfo, b.endproduct, b.tools, b.equipment, b.technicalquality, b.mes_plan_xiada_id from mes_gy_info_more b");
                        sql.append(" where b.nianyue = to_char(sysdate,'yyyy-mm')) b");
                    }
                    sql.append(" on a.objuid = b.mes_gy_info_obj ");
                    //sql.append(" left join stf_employee c on a.chargeperson = c.objuid");
                    sql.append(" left join mes_plan_xiada_fuzeren c on b.mes_plan_xiada_id = c.mes_plan_xiada_uid");
                    sql.append(" left join stf_employee d on c.stf_employee_uid = d.objuid");
                    sql.append(" where a.deleteflag = 0 and a.factory_id = #{mesGyInfoToPlanDTO.factoryId, jdbcType=VARCHAR}");
                    if (!StrUtil.isEmptyOrUndefined(mesGyInfoToPlanDTO.getJx())){
                        sql.append(" and a.jx = trim(upper(#{mesGyInfoToPlanDTO.jx, jdbcType=VARCHAR})) ");
                    }
                    if (mesGyInfoToPlanDTO.getLength() != null) {
                        sql.append(" and b.lengthnum = #{mesGyInfoToPlanDTO.length, jdbcType=INTEGER}");
                    }
                    sql.append(" group by");
                    sql.append(" b.serialnumber, a.objuid, a.jx, a.parentpartid,");
                    sql.append(" a.partid, a.djnum, a.innerwork, a.processroute, a.longperiodpart, a.deliverytype, a.parttype,");
                    sql.append(" a.factory_id, a.jsworkshop, a.deliveryorder, ");
                    sql.append(" b.delivertotal, a.deliverstate, a.jxmlstock, a.mlstocktotal, a.bcpstocknum, a.bcpstocktotal,");
                    sql.append(" a.mesprogress, a.outprocess, a.equal, a.waixie, a.waixiefactory, a.tempplannum, b.remarks1,");
                    sql.append(" b.moreinfo, a.chargeperson, b.endproduct, b.tools, b.equipment, b.technicalquality, a.blanking,");
                    sql.append(" a.outsourcing, a.outcooperation, a.plantime, a.actualtime, a.ladong, a.state, a.lltotal,");
                    sql.append(" b.parentnum, b.objuid, b.xiada");
                    if (date) {
                        sql.append(" ,b.matching, b.grouping_a, b.checktime, b.isexecutable, b.jzjh");
                    }
                    sql.append(" order by b.serialnumber");
                    SELECT(sql.toString());
                    System.out.println("queryPlanTreeSQL:" + sql.toString());
                }
            }.toString();
        }
    }

    // 修改考核日期
    @Update("update mes_gy_info a set " +
            " a.checktime = #{mesGyInfoToPlanDTO.checktime, jdbcType=VARCHAR}" +
            " where a.objuid = #{mesGyInfoToPlanDTO.objuid, jdbcType=VARCHAR}")
    int updateCheckTime(@Param("mesGyInfoToPlanDTO") MesGyInfoToPlanDTO mesGyInfoToPlanDTO);

    // 计划核对页面获取下料字段
    @Select("select b.materialbrand || ',' || b.materialspecification as blanking from " +
            "(select a.planid from mes_plan_xiada a where a.objuid = #{objuid, jdbcType=VARCHAR}) a " +
            "inner join mes_gy_info b on a.planid = b.objuid")
    String selectBlanking(@Param("objuid") String objuid);

    // 计划核对页面获取外购字段
    @Select("select b.forgedcastingscode as outsourcing from " +
            "(select a.planid from mes_plan_xiada a where a.objuid = #{objuid, jdbcType=VARCHAR}) a " +
            "inner join mes_gy_info b on a.planid = b.objuid")
    String selectOutsourcing(@Param("objuid") String objuid);


}
